USE [master]
GO

CREATE DATABASE [BizTalkReport]
GO
USE [BizTalkReport]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Orchestration] AS

SELECT 
	ApplicationName = bts_application.nvcName,
	ApplicationId = bts_application.nID,
	OrchestrationName = bts_orchestration.nvcFullName,
	OrchestrationId = bts_orchestration.nID,
	AssemblyName = bts_assembly.nvcName,
	HostName = adm_Host.Name,
	LogicalPortType = CASE 
								WHEN bts_orchestration_port.nPolarity = 1 THEN 'Send'
								WHEN bts_orchestration_port.nPolarity = 2 THEN 'Receive'
								ELSE NULL
								END,
	LogicalPortBindingType = CASE
								WHEN bts_orchestration_port.nBindingOption = 1 THEN 'Specify Later'
								WHEN bts_orchestration_port.nBindingOption = 2 THEN 'Scecify Now'
								WHEN bts_orchestration_port.nBindingOption = 3 THEN 'Direct'
								WHEN bts_orchestration_port.nBindingOption = 4 THEN 'Dynamic'
								ELSE NULL
							END,
	LogicalPortName = bts_orchestration_port.nvcName,
	ReceicePortName = CASE 
						WHEN bts_orchestration_port.nPolarity = 2 AND bts_orchestration_port.nBindingOption = 1 AND bts_receiveport.nvcName IS NULL THEN '(Unbound)'
						ELSE bts_receiveport.nvcName
						END,
	ReceivePortId = bts_receiveport.nID,
	ReceivePortActivating = CASE 
								WHEN bts_orchestration_port.nPolarity = 2 AND ActivatingPort.nID IS NOT NULL THEN 'Yes' 
								WHEN bts_orchestration_port.nPolarity = 2 AND ActivatingPort.nID IS NULL THEN 'No' 
								ELSE NULL 
								END,
	SendPortName = CASE 
						WHEN bts_orchestration_port.nPolarity = 1 AND bts_sendport.nvcName IS NOT NULL THEN bts_sendport.nvcName
						WHEN bts_orchestration_port.nPolarity = 1 AND bts_sendportgroup.nvcName IS NOT NULL THEN bts_sendportgroup.nvcName
						WHEN bts_orchestration_port.nPolarity = 1 AND bts_orchestration_port.nBindingOption <> 3 THEN '(Unbound)'
						ELSE NULL
						END,
	IsSendPortGroup = CASE 
							WHEN bts_orchestration_port.nPolarity = 1 AND bts_orchestration_port.nBindingOption = 1 AND bts_sendport.nvcName IS NOT NULL THEN 'No'
							WHEN bts_orchestration_port.nPolarity = 1 AND bts_orchestration_port.nBindingOption = 1 AND bts_sendportgroup.nvcName IS NOT NULL THEN 'Yes'
							ELSE NULL
							END,
	SendPortId = bts_sendport.nID,
	SendPortGroupId = bts_sendportgroup.nID

FROM 
	BizTalkMgmtDb..bts_orchestration WITH (NOLOCK)
	
	INNER JOIN BizTalkMgmtDb..bts_assembly WITH (NOLOCK)
	ON bts_orchestration.nAssemblyID = bts_assembly.nID
	
	INNER JOIN BizTalkMgmtDb..bts_application WITH (NOLOCK)
	ON bts_assembly.nApplicationID = bts_application.nID
	
	LEFT JOIN BizTalkMgmtDb..adm_Host WITH (NOLOCK)
	ON bts_orchestration.nAdminHostID = adm_Host.Id
	
	LEFT JOIN BizTalkMgmtDb..bts_orchestration_port WITH (NOLOCK)
	ON bts_orchestration.nID = bts_orchestration_port.nOrchestrationID
	
	LEFT JOIN BizTalkMgmtDb..bts_orchestration_port_binding WITH (NOLOCK)
	ON bts_orchestration_port.nID = bts_orchestration_port_binding.nOrcPortID
	
	LEFT JOIN BizTalkMgmtDb..bts_sendport WITH (NOLOCK)
	ON bts_orchestration_port_binding.nSendPortID = bts_sendport.nID

	LEFT JOIN BizTalkMgmtDb..bts_sendportgroup WITH (NOLOCK)
	ON bts_orchestration_port_binding.nSpgID = bts_sendportgroup.nID
	
	LEFT JOIN BizTalkMgmtDb..bts_receiveport WITH (NOLOCK)
	ON bts_orchestration_port_binding.nReceivePortID = bts_receiveport.nID
	
	LEFT JOIN BizTalkMgmtDb..bts_port_activation_operation WITH (NOLOCK)
	ON bts_orchestration.nID = bts_port_activation_operation.nOrchestrationID
	
	LEFT JOIN BizTalkMgmtDb..bts_orchestration_port ActivatingPort WITH (NOLOCK)
	ON bts_port_activation_operation.nPortID = ActivatingPort.nID
	





GO
/****** Object:  View [dbo].[OrchestrationInvocation]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[OrchestrationInvocation] AS

SELECT DISTINCT
	OrchestrationId = nOrchestrationID,
	OrchestrationName = Orchestration.OrchestrationName,
    InvokedOrchestrationId = nInvokedOrchestrationID,
	InvokedOrchestrationName = InvokedOrchestration.OrchestrationName,
    InvokeType = CASE 
						WHEN nInvokeType = 1 THEN 'Call'
						ELSE 'Start'
					END

FROM 
	BizTalkMgmtDb..bts_orchestration_invocation WITH (NOLOCK)

	INNER JOIN Orchestration
	ON bts_orchestration_invocation.nOrchestrationID = Orchestration.OrchestrationId

	INNER JOIN Orchestration InvokedOrchestration
	ON bts_orchestration_invocation.nInvokedOrchestrationID = InvokedOrchestration.OrchestrationId





GO
/****** Object:  View [dbo].[ApplicationList]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ApplicationList] AS

SELECT 
	ApplicationId = bts_application.nID,
	ApplicationName = bts_application.nvcName
FROM
	BizTalkMgmtDb..bts_application WITH (NOLOCK)




GO
/****** Object:  View [dbo].[AssemblyDependencies]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
SELECT * FROM AssemblyDependencies
*/

CREATE VIEW [dbo].[AssemblyDependencies] AS

-- Receive Location (Pipeline)
SELECT
	applicationName = AssemblyApplication.nvcName,
	applicationId = AssemblyApplication.nID,
	assemblyName = bts_assembly.nvcName,
	assemblyFullName = bts_assembly.nvcFullName,
	objectType = 'Receive Location Pipeline',
	objectName = bts_pipeline.Name,
	referencedByAppName = bts_application.nvcName,
	referencedByObjName = adm_ReceiveLocation.Name + ' (' + bts_receiveport.nvcName + ')'

FROM
	BizTalkMgmtDb..bts_assembly bts_assembly

	INNER JOIN BizTalkMgmtDb..bts_application AssemblyApplication
	ON bts_assembly.nApplicationID = AssemblyApplication.nID
	
	INNER JOIN BizTalkMgmtDb..bts_pipeline bts_pipeline
	ON bts_assembly.nID = bts_pipeline.nAssemblyID
	
	INNER JOIN BizTalkMgmtDb..adm_ReceiveLocation adm_ReceiveLocation
	ON bts_pipeline.Id = adm_ReceiveLocation.ReceivePipelineId
	OR bts_pipeline.Id = adm_ReceiveLocation.SendPipelineId
	
	INNER JOIN BizTalkMgmtDb..bts_receiveport bts_receiveport
	ON adm_ReceiveLocation.ReceivePortId = bts_receiveport.nID
	
	INNER JOIN BizTalkMgmtDb..bts_application bts_application
	ON bts_receiveport.nApplicationID = bts_application.nID

WHERE
	bts_assembly.nSystemAssembly = 0
	
UNION

-- Receive Port (Maps)
SELECT
	applicationName = AssemblyApplication.nvcName,
	applicationId = AssemblyApplication.nID,
	assemblyName = bts_assembly.nvcName,
	assemblyFullName = bts_assembly.nvcFullName,
	objectType = 'Receive Port Maps',
	objectName = bts_item.Name,
	referencedByAppName = bts_application.nvcName,
	referencedByObjName = bts_receiveport.nvcName

FROM
	BizTalkMgmtDb..bts_assembly bts_assembly

	INNER JOIN BizTalkMgmtDb..bts_application AssemblyApplication
	ON bts_assembly.nApplicationID = AssemblyApplication.nID
	
	INNER JOIN BizTalkMgmtDb..bts_item bts_item
	ON bts_assembly.nID = bts_item.assemblyid
	
	INNER JOIN BizTalkMgmtDb..bt_MapSpec bt_MapSpec
	ON bts_item.id = bt_MapSpec.itemid
	
	INNER JOIN BizTalkMgmtDb..bts_receiveport_transform bts_receiveport_transform
	ON bt_MapSpec.id = bts_receiveport_transform.uidTransformGUID
	
	INNER JOIN BizTalkMgmtDb..bts_receiveport bts_receiveport
	ON bts_receiveport_transform.nReceivePortID = bts_receiveport.nID
	
	INNER JOIN BizTalkMgmtDb..bts_application
	ON bts_receiveport.nApplicationID = bts_application.nID

WHERE
	bts_assembly.nSystemAssembly = 0
	
UNION

-- Send Port (Pipeline)
SELECT
	applicationName = AssemblyApplication.nvcName,
	applicationId = AssemblyApplication.nID,
	assemblyName = bts_assembly.nvcName,
	assemblyFullName = bts_assembly.nvcFullName,
	objectType = 'Send Port Pipeline',
	objectName = bts_pipeline.Name,
	referencedByAppName = bts_application.nvcName,
	referencedByObjName = bts_sendport.nvcName

FROM
	BizTalkMgmtDb..bts_assembly bts_assembly
	
	INNER JOIN BizTalkMgmtDb..bts_application AssemblyApplication
	ON bts_assembly.nApplicationID = AssemblyApplication.nID
	
	INNER JOIN BizTalkMgmtDb..bts_pipeline bts_pipeline
	ON bts_assembly.nID = bts_pipeline.nAssemblyID
	
	INNER JOIN BizTalkMgmtDb..bts_sendport bts_sendport
	ON bts_pipeline.Id = bts_sendport.nReceivePipelineID
	OR bts_pipeline.Id = bts_sendport.nSendPipelineID
	
	INNER JOIN BizTalkMgmtDb..bts_application
	ON bts_sendport.nApplicationID = bts_application.nID

WHERE
	bts_assembly.nSystemAssembly = 0
	
UNION

-- Send Port (Maps)
SELECT
	applicationName = AssemblyApplication.nvcName,
	applicationId = AssemblyApplication.nID,
	assemblyName = bts_assembly.nvcName,
	assemblyFullName = bts_assembly.nvcFullName,
	objectType = 'Send Port Maps',
	objectName = bts_item.Name,
	referencedByAppName = bts_application.nvcName,
	referencedByObjName = bts_item.Name

FROM
	BizTalkMgmtDb..bts_assembly bts_assembly
	
	INNER JOIN BizTalkMgmtDb..bts_application AssemblyApplication
	ON bts_assembly.nApplicationID = AssemblyApplication.nID
	
	INNER JOIN BizTalkMgmtDb..bts_item bts_item
	ON bts_assembly.nID = bts_item.assemblyid
	
	INNER JOIN BizTalkMgmtDb..bt_MapSpec bt_MapSpec
	ON bts_item.id = bt_MapSpec.itemid
	
	INNER JOIN BizTalkMgmtDb..bts_sendport_transform bts_sendport_transform
	ON bt_MapSpec.id = bts_sendport_transform.uidTransformGUID
	
	INNER JOIN BizTalkMgmtDb..bts_sendport bts_sendport
	ON bts_sendport_transform.nSendPortID = bts_sendport.nID
	
	INNER JOIN BizTalkMgmtDb..bts_application
	ON bts_sendport.nApplicationID = bts_application.nID

WHERE
	bts_assembly.nSystemAssembly = 0
	

GO
/****** Object:  View [dbo].[OrchestrationTree]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[OrchestrationTree] AS

SELECT
	ApplicationName = bts_application.nvcName,
	ApplicationId = bts_application.nID,

	rootOrcNS = rootOrc.nvcNamespace,
	rootOrcName = rootOrc.nvcName,

	child1OrcNS = child1.nvcNamespace,
	child1OrcName = child1.nvcName,

	child2OrcNS = child2.nvcNamespace,
	child2OrcName = child2.nvcName,

	child3OrcNS = child3.nvcNamespace,
	child3OrcName = child3.nvcName,

	child4OrcNS = child4.nvcNamespace,
	child4OrcName = child4.nvcName

FROM
	BizTalkMgmtDb..bts_orchestration rootOrc WITH (NOLOCK)

	INNER JOIN BizTalkMgmtDb..bts_assembly WITH (NOLOCK)
	ON rootOrc.nAssemblyID = bts_assembly.nID
	
	INNER JOIN BizTalkMgmtDb..bts_application WITH (NOLOCK)
	ON bts_assembly.nApplicationID = bts_application.nID

	LEFT JOIN BizTalkMgmtDb..bts_orchestration_invocation invoked1 WITH (NOLOCK)
	on rootOrc.nID = invoked1.nOrchestrationID

	LEFT JOIN BizTalkMgmtDb..bts_orchestration child1 WITH (NOLOCK)
	on invoked1.nInvokedOrchestrationID = child1.nID

	LEFT JOIN BizTalkMgmtDb..bts_orchestration_invocation invoked2 WITH (NOLOCK)
	on child1.nID = invoked2.nOrchestrationID

	LEFT JOIN BizTalkMgmtDb..bts_orchestration child2 WITH (NOLOCK)
	on invoked2.nInvokedOrchestrationID = child2.nID

	LEFT JOIN BizTalkMgmtDb..bts_orchestration_invocation invoked3 WITH (NOLOCK)
	on child2.nID = invoked3.nOrchestrationID

	LEFT JOIN BizTalkMgmtDb..bts_orchestration child3 WITH (NOLOCK)
	on invoked3.nInvokedOrchestrationID = child3.nID

	LEFT JOIN BizTalkMgmtDb..bts_orchestration_invocation invoked4 WITH (NOLOCK)
	on child3.nID = invoked4.nOrchestrationID

	LEFT JOIN BizTalkMgmtDb..bts_orchestration child4 WITH (NOLOCK)
	on invoked4.nInvokedOrchestrationID = child4.nID

WHERE
	rootOrc.nID NOT IN (SELECT nInvokedOrchestrationID FROM BizTalkMgmtDb..bts_orchestration_invocation WITH (NOLOCK))


GO
/****** Object:  View [dbo].[ReceivePort]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE VIEW [dbo].[ReceivePort] AS

SELECT
	ApplicationName = bts_application.nvcName,
	ApplicationId = bts_application.nID,
	ReceivePortName = bts_receiveport.nvcName,
	ReceivePortId = bts_receiveport.nID,
	ReceiveLocationIsPrimary = CASE
									WHEN adm_ReceiveLocation.IsPrimary = 1 THEN 'Yes'
									ELSE 'No'
									END,
	ReceiveLocationName = adm_ReceiveLocation.Name,
	ReceiveLocationId = adm_ReceiveLocation.Id,
	ReceiveLocationAdapter = adm_Adapter.Name,
	ReceiveLocationAdapterDetail = CASE 
										WHEN LEFT(adm_Adapter.Name, 4) = 'WCF-' AND CHARINDEX(':', adm_ReceiveLocation.InboundTransportURL) > 0 THEN UPPER(LEFT(adm_ReceiveLocation.InboundTransportURL, CHARINDEX(':', adm_ReceiveLocation.InboundTransportURL) - 1))
										ELSE NULL
										END,
	ReceiveLocationAddress = adm_ReceiveLocation.InboundTransportURL,
	ReceivePipeline = receivePipeline.Name,
	SendPipeline = sendPipeline.Name,
	ArchiveDirectory = REPLACE(REPLACE(CONVERT(VARCHAR(MAX), CONVERT(XML, adm_ReceiveLocation.ReceivePipelineData).query('/Root/Stages/Stage/Components/Component/Properties/ArchiveDirectory')), '<ArchiveDirectory vt="8">', ''), '</ArchiveDirectory>', '; ' + CHAR(10) + CHAR(13)),
	MapName = bts_item.FullName,
	InDoc = bt_MapSpec.indoc_docspec_name,
    OutDoc = bt_MapSpec.outdoc_docspec_name,	
	HostName = adm_Host.Name
	
FROM
	BizTalkMgmtDb..bts_application	WITH (NOLOCK)
	
	INNER JOIN BizTalkMgmtDb..bts_receiveport WITH (NOLOCK)
	ON bts_application.nID = bts_receiveport.nApplicationID
	
	INNER JOIN BizTalkMgmtDb..adm_ReceiveLocation WITH (NOLOCK)
	ON bts_receiveport.nID = adm_ReceiveLocation.ReceivePortId
	
	INNER JOIN BizTalkMgmtDb..adm_Adapter WITH (NOLOCK)
	ON adm_ReceiveLocation.AdapterId = adm_Adapter.Id
	
	LEFT JOIN BizTalkMgmtDb..bts_pipeline receivePipeline WITH (NOLOCK)
	ON adm_ReceiveLocation.ReceivePipelineId = receivePipeline.Id

	LEFT JOIN BizTalkMgmtDb..bts_pipeline sendPipeline WITH (NOLOCK)
	ON adm_ReceiveLocation.SendPipelineId = sendPipeline.Id
	
	LEFT JOIN BizTalkMgmtDb..bts_receiveport_transform
	ON bts_receiveport.nID = bts_receiveport_transform.nReceivePortID
	
	LEFT JOIN BizTalkMgmtDb..bt_MapSpec
	ON bts_receiveport_transform.uidTransformGUID = bt_MapSpec.id
	
	LEFT JOIN BizTalkMgmtDb..bts_item
	ON bt_MapSpec.itemid = bts_item.id
	
	INNER JOIN BizTalkMgmtDb..adm_ReceiveHandler
	ON adm_ReceiveLocation.ReceiveHandlerId = adm_ReceiveHandler.Id
	
	INNER JOIN BizTalkMgmtDb..adm_Host
	ON adm_ReceiveHandler.HostId = adm_Host.Id
	









GO
/****** Object:  View [dbo].[SendPort]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [dbo].[SendPort] AS

SELECT
	ApplicationName = bts_application.nvcName,
	ApplicationId = bts_application.nID,
	SendPortName = bts_sendport.nvcName,
	SendPortId = bts_sendport.nID,
	SendPortAdapter = ISNULL(adm_Adapter.Name, 'Dynamic'),
	SendPortAdapterDetail = CASE 
								WHEN LEFT(adm_Adapter.Name, 4) = 'WCF-' AND CHARINDEX(':', bts_sendport_transport.nvcAddress) > 0 THEN UPPER(LEFT(bts_sendport_transport.nvcAddress, CHARINDEX(':', bts_sendport_transport.nvcAddress) - 1))
								ELSE NULL
								END,
	SendPortAddress = ISNULL(bts_sendport_transport.nvcAddress, 'Dynamic'),
	ReceivePipeline = receivePipeline.Name,
	SendPipeline = sendPipeline.Name,
	MapName = bts_item.FullName,
	MapDirection = CASE
						WHEN bts_sendport_transform.bReceive = 1 THEN 'In'
						WHEN bts_sendport_transform.bReceive = 0 THEN 'Out'
						ELSE ''
					END,
	InDoc = bt_MapSpec.indoc_docspec_name,
    OutDoc = bt_MapSpec.outdoc_docspec_name,	
	SendHandler = ISNULL(adm_Host.Name, 'Dynamic'),
	DynamicPort = bts_sendport.bDynamic,
	FilterPropertyName = Filters.filterProperty,
	FilterPropertyOperator = CASE 
								WHEN Filters.filterOperator = 0 THEN '=='
								WHEN Filters.filterOperator = 1 THEN '<'
								WHEN Filters.filterOperator = 2 THEN '<='
								WHEN Filters.filterOperator = 3 THEN '>'
								WHEN Filters.filterOperator = 4 THEN '>='
								WHEN Filters.filterOperator = 5 THEN '!='
								WHEN Filters.filterOperator = 6 THEN 'Exists'
							END,
	FilterPropertyValue = Filters.filterValue
	
FROM
	BizTalkMgmtDb..bts_application WITH (NOLOCK)
	
	INNER JOIN BizTalkMgmtDb..bts_sendport WITH (NOLOCK)
	ON bts_application.nID = bts_sendport.nApplicationID
	
	LEFT JOIN BizTalkMgmtDb..bts_sendport_transport WITH (NOLOCK)
	ON bts_sendport.nID = bts_sendport_transport.nSendPortID
	AND ISNULL(bts_sendport_transport.nvcAddress, '') <> ''
	
	LEFT JOIN BizTalkMgmtDb..adm_Adapter WITH (NOLOCK)
	ON bts_sendport_transport.nTransportTypeId = adm_Adapter.Id
	
	LEFT JOIN BizTalkMgmtDb..bts_pipeline receivePipeline WITH (NOLOCK)
	ON bts_sendport.nReceivePipelineID = receivePipeline.Id

	LEFT JOIN BizTalkMgmtDb..bts_pipeline sendPipeline WITH (NOLOCK)
	ON bts_sendport.nSendPipelineId = sendPipeline.Id
	
	LEFT JOIN BizTalkMgmtDb..bts_sendport_transform WITH (NOLOCK)
	ON bts_sendport.nID = bts_sendport_transform.nSendPortID
	
	LEFT JOIN BizTalkMgmtDb..bt_MapSpec WITH (NOLOCK)
	ON bts_sendport_transform.uidTransformGUID = bt_MapSpec.id
	
	LEFT JOIN BizTalkMgmtDb..bts_item WITH (NOLOCK)
	ON bt_MapSpec.itemid = bts_item.id

	LEFT JOIN BizTalkMgmtDb..adm_SendHandler WITH (NOLOCK)
	ON bts_sendport_transport.nSendHandlerID = adm_SendHandler.Id
	
	LEFT JOIN BizTalkMgmtDb..adm_Host WITH (NOLOCK)
	ON adm_SendHandler.HostId = adm_Host.Id
	
	LEFT JOIN
	(
		SELECT
			SendPort.id,
 			filterProperty = T.c.value('@Property', 'VARCHAR(MAX)'),
 			filterOperator = T.c.value('@Operator', 'VARCHAR(MAX)'),
 			filterValue = T.c.value('@Value', 'VARCHAR(MAX)')

		FROM
			(
				SELECT
					id = bts_sendport.nID,
					filter = CONVERT(XML, bts_sendport.nvcFilter)
				FROM
 					BizTalkMgmtDb..bts_sendport WITH (NOLOCK)
 			) SendPort
		 	
 			CROSS APPLY filter.nodes('/Filter/Group/Statement') AS T(c)
	) Filters
	ON bts_sendport.nID = Filters.id
	










GO
/****** Object:  View [dbo].[SendPortGroup]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






CREATE VIEW [dbo].[SendPortGroup] AS

SELECT
	ApplicationName = bts_application.nvcName,
	ApplicationId = bts_application.nID,
	SendPortGroupName = bts_sendportgroup.nvcName, 
	SendPortGroupId = bts_sendportgroup.nID, 
	SendPortName = bts_sendport.nvcName,
	SendPortId = bts_sendport.nID,
	SendPortAdapter = ISNULL(adm_Adapter.Name, 'Dynamic'),
	SendPortAdapterDetail = CASE 
								WHEN LEFT(adm_Adapter.Name, 4) = 'WCF-' AND CHARINDEX(':', bts_sendport_transport.nvcAddress) > 0 THEN UPPER(LEFT(bts_sendport_transport.nvcAddress, CHARINDEX(':', bts_sendport_transport.nvcAddress) - 1))
								ELSE NULL
								END,
	SendPortAddress = ISNULL(bts_sendport_transport.nvcAddress, 'Dynamic'),
	ReceivePipeline = receivePipeline.Name,
	SendPipeline = sendPipeline.Name,
	MapName = bts_item.FullName,
	MapDirection = CASE
						WHEN bts_sendport_transform.bReceive = 1 THEN 'In'
						WHEN bts_sendport_transform.bReceive = 0 THEN 'Out'
						ELSE ''
					END,
	InDoc = bt_MapSpec.indoc_docspec_name,
    OutDoc = bt_MapSpec.outdoc_docspec_name,	
	SendHandler = ISNULL(adm_Host.Name, 'Dynamic'),
	DynamicPort = bts_sendport.bDynamic,
	FilterPropertyName = Filters.filterProperty,
	FilterPropertyOperator = CASE 
								WHEN Filters.filterOperator = 0 THEN '=='
								WHEN Filters.filterOperator = 1 THEN '<'
								WHEN Filters.filterOperator = 2 THEN '<='
								WHEN Filters.filterOperator = 3 THEN '>'
								WHEN Filters.filterOperator = 4 THEN '>='
								WHEN Filters.filterOperator = 5 THEN '!='
								WHEN Filters.filterOperator = 6 THEN 'Exists'
							END,
	FilterPropertyValue = Filters.filterValue
	
FROM
	BizTalkMgmtDb..bts_application WITH (NOLOCK)
	
	INNER JOIN BizTalkMgmtDb..bts_sendportgroup WITH (NOLOCK)
	ON bts_application.nID = bts_sendportgroup.nApplicationID

	INNER JOIN BizTalkMgmtDb..bts_spg_sendport WITH (NOLOCK)
	ON bts_sendportgroup.nID = bts_spg_sendport.nSendPortGroupID

	INNER JOIN BizTalkMgmtDb..bts_sendport WITH (NOLOCK)
	ON bts_spg_sendport.nSendPortID = bts_sendport.nID
	
	LEFT JOIN BizTalkMgmtDb..bts_sendport_transport WITH (NOLOCK)
	ON bts_sendport.nID = bts_sendport_transport.nSendPortID
	AND ISNULL(bts_sendport_transport.nvcAddress, '') <> ''
	
	LEFT JOIN BizTalkMgmtDb..adm_Adapter WITH (NOLOCK)
	ON bts_sendport_transport.nTransportTypeId = adm_Adapter.Id
	
	LEFT JOIN BizTalkMgmtDb..bts_pipeline receivePipeline WITH (NOLOCK)
	ON bts_sendport.nReceivePipelineID = receivePipeline.Id

	LEFT JOIN BizTalkMgmtDb..bts_pipeline sendPipeline WITH (NOLOCK)
	ON bts_sendport.nSendPipelineId = sendPipeline.Id
	
	LEFT JOIN BizTalkMgmtDb..bts_sendport_transform WITH (NOLOCK)
	ON bts_sendport.nID = bts_sendport_transform.nSendPortID
	
	LEFT JOIN BizTalkMgmtDb..bt_MapSpec WITH (NOLOCK)
	ON bts_sendport_transform.uidTransformGUID = bt_MapSpec.id
	
	LEFT JOIN BizTalkMgmtDb..bts_item WITH (NOLOCK)
	ON bt_MapSpec.itemid = bts_item.id

	LEFT JOIN BizTalkMgmtDb..adm_SendHandler WITH (NOLOCK)
	ON bts_sendport_transport.nSendHandlerID = adm_SendHandler.Id
	
	LEFT JOIN BizTalkMgmtDb..adm_Host WITH (NOLOCK)
	ON adm_SendHandler.HostId = adm_Host.Id
	
	LEFT JOIN
	(
		SELECT
			SendPortGroup.id,
 			filterProperty = T.c.value('@Property', 'VARCHAR(MAX)'),
 			filterOperator = T.c.value('@Operator', 'VARCHAR(MAX)'),
 			filterValue = T.c.value('@Value', 'VARCHAR(MAX)')

		FROM
			(
				SELECT
					id = bts_sendportgroup.nID,
					filter = CONVERT(XML, bts_sendportgroup.nvcFilter)
				FROM
 					BizTalkMgmtDb..bts_sendportgroup WITH (NOLOCK)
 			) SendPortGroup
		 	
 			CROSS APPLY filter.nodes('/Filter/Group/Statement') AS T(c)
	) Filters
	ON bts_sendportgroup.nID = Filters.id
	












GO
/****** Object:  StoredProcedure [dbo].[GetApplicationList]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetApplicationList] AS
BEGIN

	SELECT
		*
	FROM
		ApplicationList
	WHERE
		ApplicationName <> 'BizTalk.System'
	ORDER BY
		ApplicationName;

END




GO
/****** Object:  StoredProcedure [dbo].[GetAssemblyDependencies]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
EXECUTE [GetAssemblyDependencies] 3
*/
CREATE PROCEDURE [dbo].[GetAssemblyDependencies]
	@ApplicationId INT,
	@searchString VARCHAR(256) = NULL
AS
BEGIN

	SELECT 
		applicationName,
		applicationId,
		assemblyName,
		assemblyFullName,
		objectType,
		objectName,
		referencedByAppName,
		referencedByObjName
	FROM 
		AssemblyDependencies

	WHERE
		AssemblyDependencies.ApplicationId = @ApplicationId
		AND
		(
			assemblyName LIKE ISNULL('%' + @searchString + '%', assemblyName)
			OR objectName LIKE ISNULL('%' + @searchString + '%', objectName)
			OR ISNULL(referencedByObjName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(referencedByObjName, ''))
		)

	ORDER BY
		assemblyName,
		objectType,
		objectName,
		referencedByAppName,
		referencedByObjName

END




GO
/****** Object:  StoredProcedure [dbo].[GetOrchestrationInvocation]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetOrchestrationInvocation]
	@OrchestrationId INT
AS
BEGIN

	SELECT
		InvokedOrchestrationName,
		InvokeType
	FROM 
		OrchestrationInvocation
	WHERE
		OrchestrationId = @OrchestrationId
	ORDER BY
		InvokedOrchestrationName

END




GO
/****** Object:  StoredProcedure [dbo].[GetOrchestrationList]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
EXECUTE [GetOrchestrationList] 3
*/
CREATE PROCEDURE [dbo].[GetOrchestrationList]
	@ApplicationId INT,
	@searchString VARCHAR(256) = NULL
AS
BEGIN

	SELECT DISTINCT
		Orchestration_ApplicationName = Orchestration.ApplicationName,
		Orchestration_ApplicationId = Orchestration.ApplicationId,
		Orchestration_OrchestrationName = Orchestration.OrchestrationName,
		Orchestration_OrchestrationId = Orchestration.OrchestrationId,
		Orchestration_AssemblyName = Orchestration.AssemblyName,
		Orchestration_HostName = Orchestration.HostName

	FROM
		Orchestration

	WHERE
		Orchestration.ApplicationId = @ApplicationId
		AND
		(
			OrchestrationName LIKE ISNULL('%' + @searchString + '%', OrchestrationName)
			OR LogicalPortName LIKE ISNULL('%' + @searchString + '%', LogicalPortName)
			OR ISNULL(ReceicePortName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(ReceicePortName, ''))
			OR ISNULL(SendPortName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(SendPortName, ''))
		)

	ORDER BY
		Orchestration.OrchestrationName

END




GO
/****** Object:  StoredProcedure [dbo].[GetOrchestrationReceivePort]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
EXECUTE [GetOrchestrationReceivePort] 15
*/
CREATE PROCEDURE [dbo].[GetOrchestrationReceivePort]
	@OrchestrationId INT
AS
BEGIN

	SELECT DISTINCT
		Orchestration_OrchestrationName = Orchestration.OrchestrationName,
		Orchestration_OrchestrationId = Orchestration.OrchestrationId,
		Orchestration_LogicalPortType = Orchestration.LogicalPortType,
		Orchestration_LogicalPortBindingType = Orchestration.LogicalPortBindingType,
		Orchestration_LogicalPortName = Orchestration.LogicalPortName,
		Orchestration_ReceivePortActivating = Orchestration.ReceivePortActivating,
		ReceivePort_ReceivePortName = ISNULL(ReceivePort.ReceivePortName, Orchestration.ReceicePortName),
		ReceivePort_ReceivePortId = ReceivePort.ReceivePortId,
		ReceivePort_ReceiveLocationIsPrimary = ReceivePort.ReceiveLocationIsPrimary,
		ReceivePort_ReceiveLocationName = ReceivePort.ReceiveLocationName,
		ReceivePort_ReceiveLocationId = ReceivePort.ReceiveLocationId,
		ReceivePort_ReceiveLocationAdapter = ReceivePort.ReceiveLocationAdapter,
		ReceivePort_ReceiveLocationAdapterDetail = ReceivePort.ReceiveLocationAdapterDetail,
		ReceivePort_ReceiveLocationAddress = ReceivePort.ReceiveLocationAddress,
		ReceivePort_ReceivePipeline = ReceivePort.ReceivePipeline,
		ReceivePort_SendPipeline = ReceivePort.SendPipeline,
		ReceivePort_ArchiveDirectory = ReceivePort.ArchiveDirectory,
		ReceivePort_MapName = ReceivePort.MapName,
		ReceivePort_InDoc = ReceivePort.InDoc,
		ReceivePort_OutDoc = ReceivePort.OutDoc,
		ReceivePort_HostName = ReceivePort.HostName

	FROM
		Orchestration

		LEFT JOIN ReceivePort
		ON Orchestration.ReceivePortId = ReceivePort.ReceivePortId

	WHERE
		Orchestration.OrchestrationId = @OrchestrationId
		AND Orchestration.LogicalPortType = 'Receive'

	ORDER BY
		Orchestration.OrchestrationName,
		Orchestration.LogicalPortType
		
END





GO
/****** Object:  StoredProcedure [dbo].[GetOrchestrationSendPort]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
EXECUTE [GetOrchestrationSendPort] 63
*/
CREATE PROCEDURE [dbo].[GetOrchestrationSendPort]
	@OrchestrationId INT
AS
BEGIN

	SELECT DISTINCT
		Orchestration_OrchestrationName = Orchestration.OrchestrationName,
		Orchestration_OrchestrationId = Orchestration.OrchestrationId,
		Orchestration_LogicalPortType = Orchestration.LogicalPortType,
		Orchestration_LogicalPortBindingType = Orchestration.LogicalPortBindingType,
		Orchestration_LogicalPortName = Orchestration.LogicalPortName,
		SendPort_SendPortName = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN ISNULL(SendPort.SendPortName, Orchestration.SendPortName) ELSE SendPortGroup.SendPortName END,
		SendPort_SendPortGroupName = SendPortGroup.SendPortGroupName,
		SendPort_IsSendPortGroup = Orchestration.IsSendPortGroup,
		SendPort_SendPortId = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.SendPortId ELSE SendPortGroup.SendPortId END,
		SendPort_SendPortAdapter = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.SendPortAdapter ELSE SendPortGroup.SendPortAdapter END,
		SendPort_SendPortAdapterDetail = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.SendPortAdapterDetail ELSE SendPortGroup.SendPortAdapterDetail END,
		SendPort_SendPortAddress = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.SendPortAddress ELSE SendPortGroup.SendPortAddress END,
		SendPort_ReceivePipeline = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.ReceivePipeline ELSE SendPortGroup.ReceivePipeline END,
		SendPort_SendPipeline = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.SendPipeline ELSE SendPortGroup.SendPipeline END,
		SendPort_MapName = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.MapName ELSE SendPortGroup.MapName END,
		SendPort_MapDirection = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.MapDirection ELSE SendPortGroup.MapDirection END,
		SendPort_InDoc = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.InDoc ELSE SendPortGroup.InDoc END,
		SendPort_OutDoc = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.OutDoc ELSE SendPortGroup.OutDoc END,
		SendPort_SendHandler = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.SendHandler ELSE SendPortGroup.SendHandler END,
		SendPort_DynamicPort = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.DynamicPort ELSE SendPortGroup.DynamicPort END,
		SendPort_FilterPropertyName = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.FilterPropertyName ELSE SendPortGroup.FilterPropertyName END,
		SendPort_FilterPropertyOperator = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.FilterPropertyOperator ELSE SendPortGroup.FilterPropertyOperator END,
		SendPort_FilterPropertyValue = CASE WHEN Orchestration.IsSendPortGroup = 'No' THEN SendPort.FilterPropertyValue ELSE SendPortGroup.FilterPropertyValue END

	FROM
		Orchestration

		LEFT JOIN SendPort
		ON Orchestration.SendPortId = SendPort.SendPortId

		LEFT JOIN SendPortGroup
		ON Orchestration.SendPortGroupId = SendPortGroup.SendPortGroupId

	WHERE
		Orchestration.OrchestrationId = @OrchestrationId
		AND Orchestration.LogicalPortType = 'Send'

	ORDER BY
		Orchestration.OrchestrationName;

END





GO
/****** Object:  StoredProcedure [dbo].[GetOrchestrationTree]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
EXECUTE [GetOrchestrationTree] 3
*/
CREATE PROCEDURE [dbo].[GetOrchestrationTree]
	@ApplicationId INT,
	@searchString VARCHAR(256) = NULL
AS
BEGIN

	SELECT 
		ApplicationName,
		ApplicationId,
		rootOrcNS,
		rootOrcName,
		child1OrcNS,
		child1OrcName,
		child2OrcNS,
		child2OrcName,
		child3OrcNS,
		child3OrcName,
		child4OrcNS,
		child4OrcName

	FROM
		OrchestrationTree

	WHERE
		ApplicationId = @ApplicationId
		AND
		(
			rootOrcName LIKE ISNULL('%' + @searchString + '%', rootOrcName)
			OR rootOrcNS LIKE ISNULL('%' + @searchString + '%', rootOrcNS)
			OR ISNULL(child1OrcName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(child1OrcName, ''))
			OR ISNULL(child1OrcNS, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(child1OrcNS, ''))
			OR ISNULL(child2OrcName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(child2OrcName, ''))
			OR ISNULL(child2OrcNS, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(child2OrcNS, ''))
			OR ISNULL(child3OrcName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(child3OrcName, ''))
			OR ISNULL(child3OrcNS, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(child3OrcNS, ''))
			OR ISNULL(child4OrcName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(child4OrcName, ''))
			OR ISNULL(child4OrcNS, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(child4OrcNS, ''))
		)

	ORDER BY
		rootOrcNS

END




GO
/****** Object:  StoredProcedure [dbo].[GetReceivePortWithPortSubscriptions]    Script Date: 05/10/2015 16:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
EXECUTE GetReceivePortWithPortSubscriptions 5, NULL
*/

CREATE PROCEDURE [dbo].[GetReceivePortWithPortSubscriptions]
	@ApplicationId INT,
	@searchString VARCHAR(256) = NULL
AS

SELECT
	ReceivePort.ApplicationName,
	ReceivePort.ReceivePortName,
	ReceivePort.ReceiveLocationIsPrimary,
	ReceivePort.ReceiveLocationName,
	ReceivePort.ReceiveLocationAdapter,
	ReceivePort.ReceiveLocationAdapterDetail,
	ReceivePort.ReceiveLocationAddress,
	ReceivePort.ReceivePipeline,
	ReceivePort.SendPipeline,
	ReceivePort.ArchiveDirectory,
	ReceivePort.MapName,
	ReceivePort.InDoc,
	ReceivePort.OutDoc,
	ReceivePort.HostName,

	SP_SendPortApplicationName = ISNULL(SendPortGroup.ApplicationName, SendPort.ApplicationName),
	SP_SendPortGroupName = SendPortGroup.SendPortGroupName,

	SP_SendPortName = ISNULL(SendPortGroup.SendPortName, SendPort.SendPortName),
	SP_SendPortAdapter = ISNULL(SendPortGroup.SendPortAdapter, SendPort.SendPortAdapter),
	SP_SendPortAdapterDetail = ISNULL(SendPortGroup.SendPortAdapterDetail, SendPort.SendPortAdapterDetail),
	SP_SendPortAddress = ISNULL(SendPortGroup.SendPortAddress, SendPort.SendPortAddress),
	SP_ReceivePipeline = ISNULL(SendPortGroup.ReceivePipeline, SendPort.ReceivePipeline),
	SP_SendPipeline = ISNULL(SendPortGroup.SendPipeline, SendPort.SendPipeline),
	SP_MapName = ISNULL(SendPortGroup.MapName, SendPort.MapName),
	SP_MapDirection = ISNULL(SendPortGroup.MapDirection, SendPort.MapDirection),
	SP_InDoc = ISNULL(SendPortGroup.InDoc, SendPort.InDoc),
	SP_OutDoc = ISNULL(SendPortGroup.OutDoc, SendPort.OutDoc),
	SP_SendHandler = ISNULL(SendPortGroup.SendHandler, SendPort.SendHandler),

	SP_FilterPropertyName = SendPort.FilterPropertyName,
	SP_FilterPropertyOperator = SendPort.FilterPropertyOperator,
	SP_FilterPropertyValue = SendPort.FilterPropertyValue,

	SPG_FilterPropertyName = SendPortGroup.FilterPropertyName,
	SPG_FilterPropertyOperator = SendPortGroup.FilterPropertyOperator,
	SPG_FilterPropertyValue = SendPortGroup.FilterPropertyValue

FROM
	
	ReceivePort WITH (NOLOCK)

	LEFT JOIN SendPortGroup WITH (NOLOCK)
	ON ReceivePort.ReceivePortName = SendPortGroup.FilterPropertyValue

	LEFT JOIN SendPort WITH (NOLOCK)
	ON ReceivePort.ReceivePortName = SendPort.FilterPropertyValue

WHERE

	ReceivePort.ApplicationId = @ApplicationId
	AND
	(
		ReceivePort.ReceivePortName LIKE ISNULL('%' + @searchString + '%', ReceivePort.ReceivePortName)
		OR ISNULL(ReceivePort.ReceiveLocationName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(ReceivePort.ReceiveLocationName, ''))
		OR ISNULL(SendPortGroup.SendPortGroupName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(SendPortGroup.SendPortGroupName, ''))
		OR ISNULL(SendPort.SendPortName, '') LIKE ISNULL('%' + @searchString + '%', ISNULL(SendPort.SendPortName, ''))
	)
	AND
	(
		SendPortGroup.FilterPropertyName = 'BTS.ReceivePortName'
		OR SendPort.FilterPropertyName = 'BTS.ReceivePortName'	
	)

ORDER BY
	ReceivePort.ReceivePortName


GO
USE [master]
GO
ALTER DATABASE [BizTalkReport] SET  READ_WRITE 
GO
